package yunhe.dao.impl;

import yunhe.dao.NewsDao;
import yunhe.entity.AdminInfo;
import yunhe.entity.BoardInfo;
import yunhe.entity.NewsInfo;
import yunhe.factory.BaseConn;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.ArrayList;
import java.util.List;

public class NewsDaoImpl extends BaseConn implements NewsDao {

	public List<NewsInfo> getPageBy(int pageSize, int currentPage) {
		List<NewsInfo> lni = new ArrayList<NewsInfo>();
		String sql1 = "select e.* from (select t.*, rownum rn from (select * from tb_news order by nid) t where rownum <="
				+ pageSize
				* currentPage
				+ ") e where rn >="
				+ (pageSize * (currentPage - 1) + 1); //查询出了新闻
		
		
		//连接到nadmin查询出发布者,连接到n_board表查询出板块
		String sql="select * from ("+sql1+") a,n_admin b,n_board c  where a.naid=b.naid and a.bid=c.bid"; 
		PreparedStatement pst=null;
		ResultSet rs=null;
		
		try {
			pst = (PreparedStatement) setParams(sql, null, 1);
			rs = pst.executeQuery();
			while (rs.next()) {
				NewsInfo ni = new NewsInfo();
				ni.setnId(rs.getInt("nid"));
				ni.setTitle(rs.getString("title"));
				ni.setTime(rs.getString("time"));
				ni.setContext(rs.getString("context"));
				
				AdminInfo admin=new AdminInfo();
				admin.setAnId(rs.getInt("naid"));
				admin.setnaName(rs.getString("naname"));
				admin.setnState(rs.getInt("nstate"));
				
				ni.setAdmin(admin);
				
				BoardInfo board=new BoardInfo();
				board.setbId(rs.getInt("bid"));
				board.setbName(rs.getString("bname"));
				
				ni.setBoard(board);
				
				lni.add(ni);
			}
		} catch (Exception e) {
			lni = null;
		}finally{
			closeAll(pst, rs);
		}

		return lni;
	}

	
	public int getRowNum() {
		String sql="select count(nid) from tb_news";
		int rowNum=-1;
		PreparedStatement pst=null;
		ResultSet rs=null;
		try{
			pst = (PreparedStatement) setParams(sql, null, 1);
			rs = pst.executeQuery();
			rs.next();
			rowNum=rs.getInt(1);
		}catch(Exception e){
			rowNum=-1;
		}finally{
			closeAll(pst, rs);
		}
		return rowNum;
	}


	
	public int addNews(NewsInfo ni) {
		String sql="insert into tb_news values(null,?,?,null,?,?,?)";
		
		Object[] params={ni.getTitle(),ni.getContext(),ni.getAdmin().getAnId(),ni.getBoard().getbId(),ni.getPicPath()};
		int re=CUD(sql, params);
		return re;
	}
	
	public int updateNews(NewsInfo ni){
		String sql="update tb_news set title=?,context=?,naid=?,bid=?,picpath=?  where nid=?";
		
		Object[] params={ni.getTitle(),ni.getContext(),ni.getAdmin().getAnId(),ni.getBoard().getbId(),ni.getPicPath(),ni.getnId()};
		int re=CUD(sql, params);
		return re;
	}


	
	public List<NewsInfo> seachBy(String text) {
		String sql="select * from (select * from tb_news where title like ?) a,n_admin b,n_board c  where a.naid=b.naid and a.bid=c.bid";
		PreparedStatement pst=null;
		ResultSet rs=null;
		List<NewsInfo> lni=new ArrayList<NewsInfo>();
		String[] params={text+"%"};
		try{
			pst=(PreparedStatement) setParams(sql, params, 1);
			rs=pst.executeQuery();
			while(rs.next()){
				NewsInfo ni = new NewsInfo();
				ni.setnId(rs.getInt("nid"));
				ni.setTitle(rs.getString("title"));
				ni.setTime(rs.getString("time"));
				ni.setContext(rs.getString("context"));
				
				AdminInfo admin=new AdminInfo();
				admin.setAnId(rs.getInt("naid"));
				admin.setnaName(rs.getString("naname"));
				admin.setnState(rs.getInt("nstate"));
				
				ni.setAdmin(admin);
				
				BoardInfo board=new BoardInfo();
				board.setbId(rs.getInt("bid"));
				board.setbName(rs.getString("bname"));
				
				ni.setBoard(board);
				
				lni.add(ni);
			}
		}catch(Exception e){
			lni = null;
		}finally{
			closeAll(pst, rs);
		}
		return lni;
	}

}
